// #[warn(dupli)]
// #[allow(diplica)]
use crate::{/*types::Db,*/ error::DbError, models::{Post, Config, UserPost, /*MatrixOrTelegramId,*/ UserIdDb, UserPostInfo, Message}, /*queries::{UserPostInfo}*/};
#[cfg(feature = "telegram")]
use teloxide_core::types::UserId;
use serde::Deserialize;
use time::OffsetDateTime;
use uuid::Uuid;
use std::sync::Arc;
use tokio::sync::Mutex;
use tokio_stream::StreamExt;
// use tokio_postgres::{Client, NoTls, Connection, Socket, tls::NoTlsStream};
use sqlx::{postgres::PgPoolOptions, Pool, Postgres, FromRow, Row};
#[derive(Clone)]
pub struct Db{
    pool: Pool<Postgres>,
    // connection: Connection<Socket, NoTlsStream> 
}
impl Db {

/// Connect to postgres database
pub async fn connect(postgres_url: &str,) -> Result<Self, DbError> {
    let pool = PgPoolOptions::new()
    .max_connections(5)
    .connect(&postgres_url).await?;
    // let (client, connection) = tokio_postgres::connect(
    //     &format!("host={url} user={user} dbname={dbname}"),
    //     NoTls,
    // )
    // .await?;

    // tokio::spawn(async move {
    //     connection
    //         .await
    //         // .context("postgres connection error:")
    //         .unwrap();
    // });

    // let db = client.database("ReadLaterBot");
    Ok(
        Self {
             pool: pool,
            //  connection: connection
             }
            )
}
pub async fn create_post(&self, post: &Post) -> Result<Post, DbError> {
    let statement = r"
     insert into posts (real_url) values ($1)
     returning id, real_url, created, title, word_count, alternative_url
    ";
    // let row = self.client.query_one(statement, &[&id, &real_url]).await?;
    let post = sqlx::query_as::<_, Post>(statement)
        // .bind(id)
        .bind(post.real_url())
        .fetch_one(&self.pool).await?;
    // let post = Post::builder()
    // .id(row.get(0))
    // .real_url(row.get(1))
    // .created(row.get(2))
    // .title(row.get(3))
    // .word_count(row.get(4))
    // .alternative_url(row.get(5))
    // .build();
Ok(post)
}
pub async fn find_post(&self, real_url: &str) -> Result<Option<Post>, DbError> {
    let statement = r"select id, real_url, created, 
    COALESCE( 
        NULLIF (title, ''),
        title
        ) as title,
    
     word_count, alternative_url
    from posts 
    where real_url = $1";
    // let row = self.client.query_one(statement, &[&id, &real_url]).await?;
    let post = sqlx::query_as::<_, Post>(statement)
        // .bind(id)
        .bind(real_url)
        .fetch_optional(&self.pool).await?;
    // let post = Post::builder()
    // .id(row.get(0))
    // .real_url(row.get(1))
    // .created(row.get(2))
    // .title(row.get(3))
    // .word_count(row.get(4))
    // .alternative_url(row.get(5))
    // .build();
Ok(post)
}
pub async fn create_user_post(&self, id: Uuid, original_url: &str, user_id: Uuid) -> Result<UserPostInfo, DbError> {
    let statement = r"
     insert into userposts (original_url, post_id, user_id) values ($2, $1, $3)
     returning id, favorite, read
    ";
    let userpost = sqlx::query_as::<_, UserPostInfo>(statement)
    .bind(id)
    .bind(original_url)
    .bind(user_id)
    .fetch_one(&self.pool)
    .await?;
    // let userpost = UserPostInfo { id: row.get(0), favorite: row.get(1), read: row.get(2) };
Ok(userpost)
}
pub async fn find_user_post(&self, user_id: Uuid, post_id: Uuid) -> Result<Option<UserPostInfo>, DbError> {
    let statement = r"
    select (id, favorite, read) from userposts where post_id = $1 and user_id = $2
    ";
    let userpost = sqlx::query_as::<_, UserPostInfo>(statement)
    .bind(user_id)
    .bind(post_id)
    .fetch_optional(&self.pool)
    .await?;
    // let userpost = UserPostInfo { id: row.get(0), favorite: row.get(1), read: row.get(2) };
Ok(userpost)
}
#[cfg(feature = "matrix")]
pub async fn create_user_matrix(&self, user_id: &str) -> Result<Uuid, DbError> {
    let statement = r"
     insert into users (matrix_id) values ($1) returning id
    ";
    // self.client.execute(statement, &[&id]).await?;
    let id:Uuid = sqlx::query(statement)
    .bind(user_id)
    .fetch_one(&self.pool)
    .await?
    .try_get(0)?;
Ok(id)
    }
    #[cfg(feature = "telegram")]
pub async fn create_user_tg(&self, user_id: &UserId) -> Result<Uuid, DbError> {
    let statement = r"
     insert into users (telegram_id, telegram_username) values ($1, $2) returning id
    ";
    // self.client.execute(statement, &[&(id.0 as i64), &username]).await?;
    let id = sqlx::query(statement)
    .bind(user_id.0 as i64)
    // .bind(username)
    .fetch_one(&self.pool)
    .await?
    .try_get(0)?;
Ok(id)
}
#[cfg(feature = "matrix")]
pub async fn find_user_matrix(&self, user_id: &str) -> Result<Option<Uuid>, DbError> {
    let statement = r"
    select id from users where matrix_id = $1
    ";
    // self.client.execute(statement, &[&id]).await?;
    let res = sqlx::query(statement)
    .bind(user_id)
    .fetch_optional(&self.pool)
    .await?.map(|r| r.get("id"));
Ok(res)
    }
    #[cfg(feature = "telegram")]
pub async fn find_user_tg(&self, user_id: &UserId) -> Result<Option<Uuid>, DbError> {
    let statement = r"
    select id from users where telegram_id = $1
    ";
    // self.client.execute(statement, &[&(id.0 as i64), &username]).await?;
    let res = sqlx::query(statement)
    .bind(user_id.0 as i64)
    // .bind(username)
    .fetch_optional(&self.pool)
    .await?.map(|r| r.get("id"));
Ok(res)
    }
    #[cfg(feature = "telegram")]
    pub async fn get_random_post_tg(&self, user_id: &UserId) -> Result<Option<PostRandom>, DbError> {
        let statement = r"
        SELECT up.id, p.real_url, up.original_url, p.alternative_url, up.favorite, up.show_real_url,
        up.show_alternative_url, 
        COALESCE( 
        NULLIF (p.title, ''),
        NULLIF (p.title, ' '),
        p.title
        ) as title, 
        p.word_count, up.read
        from userposts as up 
        inner join posts as p on up.post_id = p.id
        inner join users as u on u.id = up.user_id
        where up.read = false and u.telegram_id = $1 order by random() limit 1";
                // self.client.query_one(&statement, &[&id.user_id()]).await
    let res =  sqlx::query_as::<_, PostRandom>(&statement)
                .bind(user_id.0 as i64)
                .fetch_optional(&self.pool)
                .await?;
    // match row {
        Ok(res)
    //     Err(e) => {
    //         None
    //     }
    //     Ok(row) => {
    //         Some(
    //             // PostRandom { 
    //             //     real_url: row.get(0.to_owned()),
    //             //     original_url: row.get(1.to_owned()),
    //             //     alternative_url: row.get(2.to_owned()),
    //             //     favorite: row.get(3.to_owned()),
    //             //     show_real_url: row.get(4.to_owned()),
    //             //     show_alternative_url: row.get(5.to_owned()),
    //             //     title: row.get(6),
    //             //     word_count: row.get(7) }
    //             PostRandom::builder()
    //             .real_url(row.get(0))
    //             .original_url(row.get(1))
    //             .alternative_url(row.get(2))
    //             .favorite(row.get(3))
    //             .show_real_url(row.get(4))
    //             .show_alternative_url(row.get(5))
    //             .title(row.get(6))
    //             .word_count(row.get(7))
    //             .build()
            // )
        // }
    // }
}
    #[cfg(feature = "matrix")]
    pub async fn get_random_post_matrix(&self, user_id: &str) -> Result<Option<PostRandom>, DbError> {
        let statement = r"
        SELECT up.id, p.real_url, up.original_url, p.alternative_url, up.favorite, up.show_real_url,
        up.show_alternative_url, 
        COALESCE( 
        NULLIF (p.title, ''),
        NULLIF (p.title, ' '),
        p.title
        ) as title, 
        p.word_count, up.read
        from userposts as up 
        inner join posts as p on up.post_id = p.id
        inner join users as u on u.id = up.user_id
        where up.read = false and u.matrix_id = ? order by random() limit 1";
                // self.client.query_one(&statement, &[&id]).await
            let res =  sqlx::query_as::<_, PostRandom>(&statement)
                .bind(user_id)
                .fetch_optional(&self.pool)
                .await?;
    // match row {
        Ok(res)
    //     Err(e) => {
    //         None
    //     }
    //     Ok(row) => {
    //         Some(
    //             // PostRandom { 
    //             //     real_url: row.get(0.to_owned()),
    //             //     original_url: row.get(1.to_owned()),
    //             //     alternative_url: row.get(2.to_owned()),
    //             //     favorite: row.get(3.to_owned()),
    //             //     show_real_url: row.get(4.to_owned()),
    //             //     show_alternative_url: row.get(5.to_owned()),
    //             //     title: row.get(6),
    //             //     word_count: row.get(7) }
    //             PostRandom::builder()
    //             .real_url(row.get(0))
    //             .original_url(row.get(1))
    //             .alternative_url(row.get(2))
    //             .favorite(row.get(3))
    //             .show_real_url(row.get(4))
    //             .show_alternative_url(row.get(5))
    //             .title(row.get(6))
    //             .word_count(row.get(7))
    //             .build()
            // )
        // }
    // }
}
pub async fn archive_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
    sqlx::query("UPDATE userposts SET read = true WHERE id = $1")
    .bind(userpost_id)
    .execute(&self.pool)
    .await?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(())
}
pub async fn unarchive_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
    sqlx::query("UPDATE userposts SET read = false WHERE id = $1")
    .bind(userpost_id)
    .execute(&self.pool)
    .await?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(())
}
pub async fn favorite_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
    sqlx::query("UPDATE userposts SET favorite = true WHERE id = $1")
    .bind(userpost_id)
    .execute(&self.pool)
    .await?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(())
}
pub async fn unfavorite_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
    sqlx::query("UPDATE userposts SET favorite = false WHERE id = $1")
    .bind(userpost_id)
    .execute(&self.pool)
    .await?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(())
}
pub async fn set_show_alternative_url(&self, userpost_id: Uuid) -> Result<(), DbError> {
    sqlx::query("UPDATE userposts SET show_alternative_url = not show_alternative_url WHERE id = $1")
    .bind(userpost_id)
    .execute(&self.pool)
    .await?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(())
}
pub async fn set_show_real_url(&self, userpost_id: Uuid) -> Result<(), DbError> {
    sqlx::query("UPDATE userposts SET show_real_url = not show_real_url WHERE id = $1")
    .bind(userpost_id)
    .execute(&self.pool)
    .await?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(())
}
pub async fn show_alternative_url(&self, userpost_id: Uuid) -> Result<bool, DbError> {
    let res = sqlx::query("SELECT show_alternative_url from userposts WHERE id = $1")
    .bind(userpost_id)
    .fetch_one(&self.pool)
    .await?
    .try_get(0)?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(res)
}
pub async fn get_alternative_url(&self, userpost_id: Uuid) -> Result<Option<String>, DbError> {
    let res = sqlx::query("SELECT p.alternative_url from posts as p
    inner join userposts as up
    on up.post_id = p.id
    WHERE up.id = $1")
    .bind(userpost_id)
    .fetch_one(&self.pool)
    .await?
    .try_get(0)?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(res)
}
pub async fn get_real_url(&self, userpost_id: Uuid) -> Result<Option<String>, DbError> {
    let res = sqlx::query("SELECT p.real_url from posts as p
    inner join userposts as up
    on up.post_id = p.id
    WHERE up.id = $1")
    .bind(userpost_id)
    .fetch_one(&self.pool)
    .await?
    .try_get(0)?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(res)
}
pub async fn show_real_url(&self, userpost_id: Uuid) -> Result<bool, DbError> {
    let res = sqlx::query("SELECT show_real_url from userposts WHERE id = $1")
    .bind(userpost_id)
    .fetch_one(&self.pool)
    .await?
    .try_get(0)?;
    // db.lock()V
    //     .await
    //     .deref()
    //     .execute(
    //         "UPDATE userposts SET read = true WHERE id = $1",
    //         &[&userpost_id],
    //     )
    //     .await?;

    Ok(res)
}
pub async fn delete_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
    sqlx::query("DELETE FROM userposts WHERE post_id = $1")
    .bind(userpost_id)
    .execute(&self.pool)
    .await?;
    // db.lock()
    //     .await
    //     .deref()
    //     .query(
        //     &[&userpost_id],
        // )
        // .await?;

    Ok(())
}
pub async fn is_read(&self, userpost_id: &Uuid) -> Result<bool, DbError> {
    // let res = db
    //     .lock()
    //     .await
    //     .deref()
    //     .query_one(
    let res: (bool, ) = sqlx::query_as("SELECT read FROM userposts WHERE id = $1")
    .bind(userpost_id)
    .fetch_one(&self.pool)
    .await?;
        //     &[&userpost_id],
        // )
        // .await?
        // .get(0);
    Ok(res.0)
}
pub async fn is_favorite(&self, userpost_id: &Uuid) -> Result<bool, DbError> {
    // let res = db
    //     .lock()
    //     .await
    //     .deref()
    //     .query_one(
    let res: (bool, ) = sqlx::query_as("SELECT favorite FROM userposts WHERE id = $1")
    .bind(userpost_id)
    .fetch_one(&self.pool)
    .await?;
        //     &[&userpost_id],
        // )
        // .await?
        // .get(0);
    Ok(res.0)
}
pub async fn get_matrix_config(&self) -> Result<Option<Config>, DbError> {
    //     let query = db.clone().lock()
    // .await
    // .query_one(
        let query: Option<Config> = sqlx::query_as("select access_token, device_id from matrix_config")
        .fetch_optional(&self.pool)
        .await?;//, &[]).await;
    // if let Ok(row) = query{
    //     Some(Config{
    //         access_token: row.get(0),
    //         device_id: row.get(1),
    //     })
    // } else {
    //     None
    // }
    Ok(query)
}
pub async fn save_matrix_config(&self, access_token: &str, device_id: &str) -> Result<(), DbError>{
    sqlx::query("insert into matrix_config (access_token, device_id) values ($1, $2) ")
    .bind(access_token)
    .bind(device_id)
    .execute(&self.pool)
    .await?;
Ok(())
}
pub async fn create_message_event(
    &self,
    userpost_id: Uuid,
    event_id: String,
) -> Result<(), DbError> {
    let message = Message {
        event_id,
        userpost_id,
        sent: OffsetDateTime::now_utc()
    };
    /* ::builder()
        .userpost_id(userpost_id)
        .event_id(event_id)
        .sent(OffsetDateTime::now_utc())
        .build();*/
    // db.lock()
    //     .await
    //     .deref()
    //     .query(
            sqlx::query("INSERT INTO messages (userpost_id, event_id, sent) VALUES ($1, $2, $3)")
            .bind(message.userpost_id())
            .bind(message.event_id())
            .bind(&message.sent())
            .execute(&self.pool)
            .await?;
        Ok(())
    //         &[&message.userpost_id(), &message.event_id(), &message.sent()],
    //     )
    //     .await?;
    // Ok(())
}
#[cfg(feature = "matrix")]
pub async fn count_unread_time_db_matrix(&self, user_id: &str) -> Result<i32, DbError>{
    let statement = r"SELECT COALESCE(SUM(up.word_count), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
     WHERE read = false AND u.matrix_id = $1";
    let res:(i32, ) = sqlx::query_as(&statement).bind(user_id).fetch_one(&self.pool).await?;
    Ok(res.0)
        }
#[cfg(feature = "telegram")]
pub async fn count_unread_time_db_tg(&self, user_id: &UserId) -> Result<i32, DbError>{
    let statement = r"SELECT COALESCE(SUM(up.word_count), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
    WHERE read = false and u.telegram_id = $1";
    let res:(i32, ) = sqlx::query_as(&statement).bind(user_id.0 as i64).fetch_one(&self.pool).await?;
    Ok(res.0)
}
#[cfg(feature = "matrix")]
pub async fn count_unread_posts_db_matrix(&self, user_id: &str) -> Result<i32, DbError>{
    let statement = r"SELECT COALESCE(COUNT(up.id), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
     WHERE read = false AND u.matrix_id = $1";
    let res:(i32, ) = sqlx::query_as(&statement).bind(user_id).fetch_one(&self.pool).await?;
    Ok(res.0)
        }
        #[cfg(feature = "telegram")]
pub async fn count_unread_posts_db_tg(&self, user_id: &UserId) -> Result<i32, DbError>{
    let statement = r"SELECT COALESCE(COUNT(up.id), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
     WHERE read = false AND u.telegram_id = $1";
    let res:(i32, ) = sqlx::query_as(&statement).bind(user_id.0 as i64).fetch_one(&self.pool).await?;
    Ok(res.0)
}
#[cfg(feature = "matrix")]
pub async fn get_user_id_matrix(&self, user_id: &str) -> Result<Uuid, DbError> {
        let res :(Uuid, )= sqlx::query_as("SELECT id FROM users WHERE matrix_id = $1")
        .bind(user_id)
        .fetch_one(&self.pool)
        .await?;
        Ok(res.0)
    }
    #[cfg(feature = "telegram")] 
pub async fn get_user_id_tg(&self, user_id: &UserId) -> Result<Uuid, DbError> {
let id = user_id.0 as i64;
        let res:(Uuid,)= sqlx::query_as("SELECT id FROM users WHERE telegram_id = $1")
        .bind(id)
        .fetch_one(&self.pool)
        .await?;
        Ok(res.0)
}
pub async fn get_userpost_id(&self, event_id: String) -> Result<Option<Uuid>, DbError> {
    // let rows = db
    //     .lock()
    //     .await
    //     .deref()
        let res:(Option<Uuid>, ) = sqlx::query_as(
            "SELECT userpost_id FROM messages WHERE event_id = $1"
        )
        .bind(event_id)
        .fetch_one(&self.pool)
        .await?;
    Ok(res.0)
}

}
// use typed_builder::TypedBuilder;
#[derive(Debug, Deserialize, Clone, FromRow)]
pub struct PostRandom {
    pub id: Uuid,
    pub real_url: String,
    pub original_url: String,
    pub alternative_url: Option<String>,
    pub favorite: bool,
    pub show_real_url: bool,
    pub show_alternative_url: bool,
    pub title: Option<String>,
    pub word_count: Option<i64>,
    pub read: bool,
}
// impl PostRandom <'a> {
//     fn new( real_url: &str, original_url: &str, alternative_url: &str, )
// }